#!/usr/bin/env python
# coding: utf-8

# In[1]:


import pandas as pd 
import pymysql

# 获取用户信息
try:
    conn = pymysql.connect(host="localhost", user="test", passwd="123456", db='course', charset="utf8")
    cursor = conn.cursor()
    print("数据库连接成功！")
except Exception as e:
    print(e)

sql = 'select * from 6_task_training_master;'
try:
    cursor.execute(sql)
    res = cursor.fetchall()
    cols = [cursor.description[i][0] for i in range(len(cursor.description))]
    df = pd.DataFrame(res, columns=cols)
    print("数据获取成功！")
    cursor.close()
    conn.commit()
    conn.close()
except Exception as e:
    print(e)
    

import argparse
import numpy as np

# 初始化参数构造器
parser = argparse.ArgumentParser()

# 在参数构造器中添加两个命令行参数
parser.add_argument('--filename', type=str,default="第三方信息")

# 获取所有的命令行参数
args = parser.parse_args(args=[])
#args=[]

filename = args.filename # 文件名


# In[18]:


df_Third = df[['Idx'] + [i for i in df.columns if i.startswith("Third")]] # 获取第三方平台信息
# df_Third["Idx"].value_counts() # 检验客户唯一性：的确是唯一性
var_top_20 = df_Third.drop(labels=["Idx"], axis=1).var().sort_values(ascending=False)[:20]  # 按列求方差，并截取前20个，然后降序排列

# 可视化
import warnings;warnings.filterwarnings("ignore")
from matplotlib import pyplot as plt
import seaborn as sns
plt.rcParams["font.sans-serif"] = ["SimHei"]  # 正常显示中文
plt.rcParams["axes.unicode_minus"] = False # 正常显示负号

plt.figure(figsize=(10,6), dpi=100)
sns.barplot(x=var_top_20.index, y=var_top_20.values)
plt.ylabel("方差")
plt.xlabel("指标")
plt.title("前20大第三方信息方差")
plt.xticks(rotation=90)
# plt.show()
plt.tight_layout()
plt.savefig(filename + ".png")


# In[70]:


df_master = df.copy()

## 对每位客户进行分组聚合：构建96个新特征
# 聚合相同i(1-7)
for i in range(1,8):
    col = f"ThirdParty_Info_Period{i}"
    df_master["ThirdParty_Info_Period"+str(i)+"_median"] = df_Third[[i for i in df_Third.columns if i.startswith(col)]].median(axis=1)
    df_master["ThirdParty_Info_Period"+str(i)+"_std"] = df_Third[[i for i in df_Third.columns if i.startswith(col)]].std(axis=1)
    df_master["ThirdParty_Info_Period"+str(i)+"_max"] = df_Third[[i for i in df_Third.columns if i.startswith(col)]].max(axis=1)
    df_master["ThirdParty_Info_Period"+str(i)+"_min"] = df_Third[[i for i in df_Third.columns if i.startswith(col)]].min(axis=1)
    
    

# 聚合相同j（1-17）
for j in range(1,18):
    lst = []
    for i in range(1,8):
        col = f"ThirdParty_Info_Period{i}_{j}"
        lst.append(col)
    df_master[f"ThirdParty_Info_Periodi_{j}"+"_median"] = df_Third[lst].median(axis=1)
    df_master[f"ThirdParty_Info_Periodi_{j}"+"_std"] = df_Third[lst].std(axis=1)
    df_master[f"ThirdParty_Info_Periodi_{j}"+"_max"] = df_Third[lst].max(axis=1)
    df_master[f"ThirdParty_Info_Periodi_{j}"+"_min"] = df_Third[lst].min(axis=1)


# In[75]:


# 写入数据库
import pymysql
from sqlalchemy import create_engine
host = "localhost"
user = "root" # 用户名
passwd=""  # 密码
db='course' # 数据库名
engine = create_engine(f"mysql+pymysql://{user}:{passwd}@{host}/{db}?charset=utf8")
df_master.to_sql("df_master", con=engine, if_exists="replace", index=False)

